<%@ page pageEncoding="UTF-8" %>
<%!
  /**
   * 每个小模块下的都有此文件，用来封装后台方法，这样方法之间调用会比较好，但是耦合性太强，
   * 测试文件单独放一个文件夹
   */

  //判断是否存在该医院组织架构数据
  public Message organizationCheck(Input input){
    Integer hospital = input.getInt("hospital",-999);
    Integer deptType = input.getInt("deptType",-1);
    Message msg = new Message();
    List<Long> dataList = QBuilder.db("basedb").queryHql("select count(*) from Dept where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE);
    msg.put("data",dataList.get(0));//数目为0表示没有存储过数据，不为0则表示存储过数据，需要提示是否确认覆盖
    return msg;
  }

  /**
   * 导入组织架构的excel数据，因为有父子关系，所以无法批量存储，因为无法获取到父科室id
   * @param input
   * @return
   * @throws Exception
   */
  public Message organizationExcel(Input input) throws Exception{
    String urlFileName = input.getDesc("urlFileName","");
    Integer hospital = input.getInt("hospital",-999);
    Integer deptType = input.getInt("deptType",-1);
    Message msg = new Message();//用于存储结果数据
    String fileName = urlFileName.substring(urlFileName.lastIndexOf("/") + 1);
    urlFileName = urlFileName.substring(0,urlFileName .lastIndexOf("/"))+"/"+java.net.URLEncoder.encode(fileName,"utf-8");
    jxl.Workbook workbook;
    InputStream is = null;
    Integer errNum = 0;
    try{
      URL url = new URL(urlFileName);//获取excel地址对象
      URLConnection urlConnection = url.openConnection();
      urlConnection.connect();
      HttpURLConnection httpURLConnection = (HttpURLConnection) urlConnection;
      if(httpURLConnection.getResponseCode() != HttpURLConnection.HTTP_OK){
        msg.setErrcodeAndErrmsg(23333,"URL地址解析异常");
        return msg;
      }else{
        //解析正确，首先逻辑删除所有的组织架构
        QBuilder.db("basedb").execHQL("update Dept set status="+STATUS_DISABLE+" where hospital="+hospital+" and type="+deptType);
        //逻辑删除标签关联表,type=2 表示科室关联
        QBuilder.db("basedb").execHQL("update hospital_label_member set status="+STATUS_DISABLE+" where hospital="+hospital+" and type=2");
        //读取文件流
        is = httpURLConnection.getInputStream();
        workbook = jxl.Workbook.getWorkbook(is);
        jxl.Sheet sheet = workbook.getSheet(0);//获取第一张sheet表
        int rows = sheet.getRows();//获取行数
        List<String> errList = new ArrayList<String>();//用于存放错误列表
        Dept addDept;//用于存放单条存储记录
        for(int r=1;r<rows;r++){//循环获取数据记录
          errNum = r;
          String deptName = sheet.getCell(0,r).getContents();//部门名称
          String parentDeptName = sheet.getCell(1,r).getContents();//上级部门名称
          //判断科室是否为空
          if(deptName.equals("")){
            //部门名称为空，跳过该条记录
            msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行数据异常，部门名称为空");
            //物理删除科室新增数据
            QBuilder.db("basedb").execHQL("delete from Dept where status="+STATUS_ENABLE+" and hospital="+hospital+" and type="+deptType);
            //逻辑恢复科室表，type=20 表示通讯录科室
            QBuilder.db("basedb").execHQL("update Dept set status="+STATUS_ENABLE+" where hospital="+hospital+" and type="+deptType);
            //逻辑恢复标签关联表,type=2 表示科室关联
            QBuilder.db("basedb").execHQL("update hospital_label_member set status="+STATUS_ENABLE+" where hospital="+hospital+" and type=2");
            return msg;
          }else{
            //记录正常，查询该部门记录是否已经存在，考虑到重名情况
            List<Map> deptCheck = QBuilder.db("basedb").queryHql("select new map(deptName as deptName) from Dept where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE+" and deptName='"+deptName+"' and parent=(select id from Dept where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE+" and deptName='"+parentDeptName+"')");
            if(deptCheck.size()>0){
              //说明有记录，则跳过这条
              msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行数据异常，部门名称重复");
              //物理删除科室新增数据
              QBuilder.db("basedb").execHQL("delete from Dept where status="+STATUS_ENABLE+" and hospital="+hospital+" and type="+deptType);
              //逻辑恢复科室表，type=20 表示通讯录科室
              QBuilder.db("basedb").execHQL("update Dept set status="+STATUS_ENABLE+" where hospital="+hospital+" and type="+deptType);
              //逻辑恢复标签关联表,type=2 表示科室关联
              QBuilder.db("basedb").execHQL("update hospital_label_member set status="+STATUS_ENABLE+" where hospital="+hospital+" and type=2");
              return msg;
            }else{
              //存储记录，可首先确定字段
              addDept = new Dept();
              addDept.setDeptName(deptName);//先存放部门名称
              addDept.setType(deptType);//部门类别
              addDept.setHospital(hospital);//医院编号
              //如果上级部门字段为空或者无，则不需要判断，直接存储
              if(parentDeptName.equals("")||parentDeptName.equals("无")){
                addDept.setParent("-1");//存放上级部门id为-1
                DBTools.db("basedb").addItem(addDept);
              }else{
                //判断parentDeptName
                //查询上级部门所属id
                List<Integer> parentList = QBuilder.db("basedb").queryHql("select id from Dept where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE+" and deptName='"+parentDeptName+"'");
                if(parentList.size()>0){
                  //存在上级部门，获取id
                  Integer parentDeptId = parentList.get(0);
                  //存入list
                  addDept.setParent(parentDeptId+"");//存放上级部门id
                  DBTools.db("basedb").addItem(addDept);
                }else{
                  //不存在上级部门记录，报错
                  msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行数据异常，不存在该上级部门记录");
                  //物理删除科室新增数据
                  QBuilder.db("basedb").execHQL("delete from Dept where status="+STATUS_ENABLE+" and hospital="+hospital+" and type="+deptType);
                  //逻辑恢复科室表，type=20 表示通讯录科室
                  QBuilder.db("basedb").execHQL("update Dept set status="+STATUS_ENABLE+" where hospital="+hospital+" and type="+deptType);
                  //逻辑恢复标签关联表,type=2 表示科室关联
                  QBuilder.db("basedb").execHQL("update hospital_label_member set status="+STATUS_ENABLE+" where hospital="+hospital+" and type=2");
                  return msg;
                }
              }
            }
          }
        }
        //批量更新deptId
        QBuilder.db("basedb").execHQL("update Dept set deptId=id where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE);
        //物理删除科室原数据
        QBuilder.db("basedb").execHQL("delete from Dept where status="+STATUS_DISABLE+" and hospital="+hospital+" and type="+deptType);
      }
    }catch(Exception e){
      msg.setErrcodeAndErrmsg(23333, "第" + (errNum + 1) + "行数据异常");
      //物理删除科室新增数据
      QBuilder.db("basedb").execHQL("delete from Dept where status="+STATUS_ENABLE+" and hospital="+hospital+" and type="+deptType);
      //逻辑恢复科室表，type=20 表示通讯录科室
      QBuilder.db("basedb").execHQL("update Dept set status="+STATUS_ENABLE+" where hospital="+hospital+" and type="+deptType);
      //逻辑恢复标签关联表,type=2 表示科室关联
      QBuilder.db("basedb").execHQL("update hospital_label_member set status="+STATUS_ENABLE+" where hospital="+hospital+" and type=2");
      e.printStackTrace();
    }finally{
      if(is != null){//关闭文件流
        is.close();
      }
      return msg;//返回结果
    }
  }

  //读取组织架构
  public Message organizationQuery(Input input){
    Integer hospital = input.getInt("hospital",-999);
    Integer deptType = input.getInt("deptType",-1);
    Message msg = new Message();
    //获取所有记录
    List<Map> allList = QBuilder.db("basedb").queryHql("select new map(id as id,deptName as deptName,parent as parent) from Dept where hospital="+hospital+" and type="+deptType+" and status="+STATUS_ENABLE);
    //处理层级结构
    List<Map> deptList = new ArrayList<Map>();//存放层级结果
    for(int x=0;x<allList.size();x++){//遍历list
      Map deptMap = allList.get(x);//拿到map
      String deptId = deptMap.get("id")+"";//获取部门id
      String parent = deptMap.get("parent")+"";//获取上级部门id
      List<Map> subList = new ArrayList<Map>();//存放该map的子集list
      if(parent.equals("-1")){//如果parent为-1，则该map不会出现在子集map里面去，删除该元素
        deptList.add(deptMap);
        allList.remove(x);
      }
      for(int y=0;y<allList.size();y++){//遍历子list
        Map subDeptMap = allList.get(y);//拿到子集map
        String subParent = subDeptMap.get("parent")+"";//获取上级部门id
        if(deptId.equals(subParent)){//满足条件，是x的子集，存到子list中
          subList.add(subDeptMap);
        }else{//不满足条件，跳过
          continue;
        }
      }
      deptMap.put("sub_dept",subList);//将子list存入
    }
    msg.put("data",deptList);
    return msg;
  }
%>
